package dao;

import pojo.Cate;
import util.DBUtil;
import util.DBUtilImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author: zen
 * @date: 2020/4/15
 * @Title: CateDaoImpl
 * @Description:
 */
public class CateDaoImpl implements CateDao {

    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
    private DBUtil dbUtil = DBUtilImpl.getSingleton();

    /**
     * 获取美食列表
     *
     * @return
     */
    @Override
    public ArrayList<Cate> getAllCates() {
        ArrayList<Cate> list = new ArrayList<>();
        conn = dbUtil.getConnection();
        // 如果没有获取连接，则结束方法
        if (conn == null) {
            return list;
        }
        // 用户美食信息（包括用户信息）
        String sql = "select cate.*, user.name from cate, user where `cate`.user_id = `user`.user_id";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Cate row = new Cate();
                row.setCateName(rs.getString("cate_name"));
                row.setCateType(rs.getString("cate_type"));
                row.setCateImage(rs.getString("cate_image"));
                row.setFoodMaterial(rs.getString("food_material"));
                row.setIngredients(rs.getString("ingredients"));
                row.setElapsedTime(rs.getString("elapsed_time"));
                row.setLevel(rs.getString("level"));
                row.setGuide(rs.getString("guide"));
                row.setUserName(rs.getString("name"));
                row.setLike(rs.getInt("like"));
                row.setPageView(rs.getInt("page_view"));
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbUtil.close(conn, pstmt, rs);
        }
        return list;
    }


    /**
     * 根据美食id获取美食信息
     * @param id
     * @return
     */
    @Override
    public Cate getCateById(int id) {
        Cate cate = null;
        conn = dbUtil.getConnection();
        if (conn == null) {
            return cate;
        }
        String sql = "select * from cate where cate_id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                cate = new Cate();
                cate.setCateId(rs.getInt("cate_id"));
                cate.setCateName(rs.getString("cate_name"));
                cate.setCateType(rs.getString("cate_type"));
                cate.setCateImage(rs.getString("cate_image"));
                cate.setFoodMaterial(rs.getString("food_material"));
                cate.setIngredients(rs.getString("ingredients"));
                cate.setElapsedTime(rs.getString("elapsed_time"));
                cate.setLevel(rs.getString("level"));
                cate.setGuide(rs.getString("guide"));
                cate.setUserId(rs.getInt("user_id"));
                cate.setLike(rs.getInt("like"));
                cate.setPageView(rs.getInt("page_view"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbUtil.close(conn, pstmt, rs);
        }
        return cate;
    }

    /**
     * 添加美食
     * @return
     */
    @Override
    public boolean addCate(Cate cate) {
        boolean flag = true;
        conn = dbUtil.getConnection();
        // 没有连接则结束方法
        if (conn == null) {
            return false;
        }
        String sql = "insert into cate(cate_name, cate_type, cate_image, food_material, ingredients, elapsed_time, level, guide, user_id)values(?,?,?,?,?,?,?,?,?)";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, cate.getCateName());
            pstmt.setString(2, cate.getCateType());
            pstmt.setString(3, cate.getCateImage());
            pstmt.setString(4, cate.getFoodMaterial());
            pstmt.setString(5, cate.getIngredients());
            pstmt.setString(6, cate.getElapsedTime());
            pstmt.setString(7, cate.getLevel());
            pstmt.setString(8, cate.getGuide());
            pstmt.setInt(9, cate.getUserId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            flag = false;
        } finally {
            dbUtil.close(conn, pstmt, rs);
        }
        return flag;
    }

    /**
     * 修改美食信息
     * @param cate
     * @return
     */
    @Override
    public boolean editCate(Cate cate) {
        boolean flag = true;
        conn = dbUtil.getConnection();
        if (conn == null) {
            return false;
        }
        String sql = "update cate set cate_name=?, cate_type=?, cate_image=?, " +
                "food_material=?, ingredients=?, elapsed_time=?, level=?, guide=? where cate_id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, cate.getCateName());
            pstmt.setString(2, cate.getCateType());
            pstmt.setString(3, cate.getCateImage());
            pstmt.setString(4, cate.getFoodMaterial());
            pstmt.setString(5, cate.getIngredients());
            pstmt.setString(6, cate.getElapsedTime());
            pstmt.setString(7, cate.getLevel());
            pstmt.setString(8, cate.getGuide());
            pstmt.setInt(9, cate.getCateId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            flag = false;
        } finally {
            // finally 是一定会执行的！！不管前面的try……catch有没有return
            dbUtil.close(conn, pstmt, rs);
        }
        return flag;
    }

    /**
     * 获取用户分享的美食
     * @param id
     * @return
     */
    @Override
    public ArrayList<Cate> getCatesByUserId(int id) {
        ArrayList<Cate> list = new ArrayList<>();
        conn = dbUtil.getConnection();
        // 如果没有获取连接，则结束方法
        if (conn == null) {
            return list;
        }
        // 用户美食信息（包括用户信息）
        String sql = "select cate.*, user.name from cate, user where cate.user_id = user.user_id and user.user_id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            Cate row = null;
            while (rs.next()) {
                row = new Cate();
                row.setCateId(rs.getInt("cate_id"));
                row.setCateName(rs.getString("cate_name"));
                row.setCateType(rs.getString("cate_type"));
                row.setCateImage(rs.getString("cate_image"));
                row.setFoodMaterial(rs.getString("food_material"));
                row.setIngredients(rs.getString("ingredients"));
                row.setElapsedTime(rs.getString("elapsed_time"));
                row.setLevel(rs.getString("level"));
                row.setGuide(rs.getString("guide"));
                row.setUserName(rs.getString("name"));
                row.setLike(rs.getInt("like"));
                row.setPageView(rs.getInt("page_view"));
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbUtil.close(conn, pstmt, rs);
        }
        return list;
    }

    @Override
    public int delCate(int id) {
        int flag = 0;
        conn = dbUtil.getConnection();
        if (conn == null) {
            return flag;
        }
        String sql = "delete from cate where cate_id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            flag = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbUtil.close(conn, pstmt, rs);
        }
        return flag;
    }


    /**
     * 根据key模糊查询cate，每次取两条数据
     * @param key   菜名
     * @return  菜肴列表
     */
    @Override
    public ArrayList<Cate> getCatesByFuzzyQuery(String key, int offset) {
        ArrayList<Cate> list = new ArrayList<>();
        conn = dbUtil.getConnection();
        // 如果没有获取连接，则结束方法
        if (conn == null) {
            return list;
        }
        // 用户美食信息（包括用户信息）
        String sql = "select cate.*, user.name from cate, user where cate.user_id = user.user_id and cate.cate_name like \"%\"?\"%\" limit " + offset+ " , 2";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, key);
            rs = pstmt.executeQuery();
            Cate row = null;
            while (rs.next()) {
                row = new Cate();
                row.setCateId(rs.getInt("cate_id"));
                row.setCateName(rs.getString("cate_name"));
                row.setCateType(rs.getString("cate_type"));
                row.setCateImage(rs.getString("cate_image"));
                row.setFoodMaterial(rs.getString("food_material"));
                row.setIngredients(rs.getString("ingredients"));
                row.setElapsedTime(rs.getString("elapsed_time"));
                row.setLevel(rs.getString("level"));
                row.setGuide(rs.getString("guide"));
                row.setUserName(rs.getString("name"));
                row.setLike(rs.getInt("like"));
                row.setPageView(rs.getInt("page_view"));
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbUtil.close(conn, pstmt, rs);
        }
        return list;
    }
}
